package com.shijichaoxing.dao.impl;

import com.shijichaoxing.dao.CandidateDao;
import com.shijichaoxing.entity.Candidate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

/**
 * @author zj
 */
@Repository
public class CandidateDaoImpl implements CandidateDao {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public boolean addCandidate(Candidate candidate) {
        String sql = "INSERT INTO candidate(" +
                "real_name, " +
                "password, " +
                "sex, " +
                "birthday, " +
                "tel, " +
                "email, " +
                "address, " +
                "resume_url, " +
                "graduation_date, " +
                "employ_time, " +
                "interviewer_id" +
                ") VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        int result = jdbcTemplate.update(sql, ps -> {
            ps.setString(1, candidate.getRealName());
            ps.setString(2, candidate.getPassword());
            ps.setString(3, candidate.getSex());
            ps.setDate(4, candidate.getBirthday());
            ps.setString(5, candidate.getTel());
            ps.setString(6, candidate.getEmail());
            ps.setString(7, candidate.getAddress());
            ps.setString(8, candidate.getResumeUrl());
            ps.setDate(9, candidate.getGraduationDate());
            ps.setDate(10, candidate.getEmployTime());
            ps.setInt(11, candidate.getInterviewerId());
        });
        return result > 0;
    }

    @Override
    public boolean updateCandidate(Candidate candidate) {
        String sql = "UPDATE candidate SET " +
                "real_name = ? ," +
                "password = ? ," +
                "sex = ? ," +
                "birthday = ? ," +
                "tel = ? ," +
                "email = ? ," +
                "address = ? ," +
                "resume_url = ? ," +
                "graduation_date = ? ," +
                "employ_time = ? ," +
                "interviewer_id = ? ," +
                "comment = ? " +
                "WHERE id = ?";
        int result = jdbcTemplate.update(sql, ps -> {
            ps.setString(1, candidate.getRealName());
            ps.setString(2, candidate.getPassword());
            ps.setString(3, candidate.getSex());
            ps.setDate(4, candidate.getBirthday());
            ps.setString(5, candidate.getTel());
            ps.setString(6, candidate.getEmail());
            ps.setString(7, candidate.getAddress());
            ps.setString(8, candidate.getResumeUrl());
            ps.setDate(9, candidate.getGraduationDate());
            ps.setDate(10, candidate.getEmployTime());
            ps.setInt(11, candidate.getInterviewerId());
            ps.setString(12, candidate.getComment());
            ps.setInt(13, candidate.getId());
        });
        return result > 0;
    }

    @Override
    public Candidate findCandidateById(Integer id) {
        String sql = "select * from candidate where id = ?";
        return jdbcTemplate.queryForObject(sql, new CandidateRowMapper(), id);
    }

    @Override
    public List<Candidate> findCandidateByPage(int start, int limit, String indexName, boolean isAsc) {
        String upDown = isAsc ? "ASC" : "DESC";
        String sql = "select * from candidate ORDER BY %s %s limit ?,?";
        return jdbcTemplate.query(String.format(sql, indexName, upDown), ps -> {
            ps.setInt(1, start);
            ps.setInt(2, limit);
        }, new CandidateRowMapper());
    }

    @Override
    public int countAll() {
        String sql = "SELECT COUNT(*) FROM candidate";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public boolean deleteById(Integer id) {
        String sql = "DELETE FROM candidate WHERE id = ?";
        int result = jdbcTemplate.update(sql, ps -> ps.setLong(1, id));
        return result > 0;
    }

    class CandidateRowMapper implements RowMapper<Candidate> {
        @Override
        public Candidate mapRow(ResultSet rs, int i) throws SQLException {
            Candidate candidate = new Candidate();
            candidate.setId(rs.getInt("id"));
            candidate.setRealName(rs.getString("real_name"));
            candidate.setPassword(rs.getString("password"));
            candidate.setSex(rs.getString("sex"));
            candidate.setBirthday(rs.getDate("birthday"));
            candidate.setTel(rs.getString("tel"));
            candidate.setEmail(rs.getString("email"));
            candidate.setAddress(rs.getString("address"));
            candidate.setResumeUrl(rs.getString("resume_url"));
            candidate.setGraduationDate(rs.getDate("graduation_date"));
            candidate.setEmployTime(rs.getDate("employ_time"));
            candidate.setInterviewerId(rs.getInt("interviewer_id"));
            candidate.setComment(rs.getString("comment"));
            candidate.setCreateTime(rs.getTimestamp("create_time"));
            candidate.setUpdateTime(rs.getTimestamp("update_time"));
            return candidate;
        }
    }
}
